<?php

namespace App\Console\Commands;

use App\Models\DataDict;
use App\Models\District;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

class initDicts extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'app:init-dicts';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Command description';

    /**
     * Execute the console command.
     */
    public function handle()
    {
        echo "\n字典初始化开始...\n";
        DB::statement("truncate data_dicts;");
        DB::statement("truncate districts;");
        echo "\n字典表清空...\n";
        $this->initReportingUnit();
        $this->initHotelRestaurant();
        $this->initFamilyHotel();
        $this->initHighSchoolGraduates();
        $this->initTourismProduct();
        $this->initProjectConstruction();
        $this->initTravelAgency();
        $this->initStarredHotel();
        $this->initALevelScenicSpot();

        echo "\n字典初始化结束...\n";
        exit;
    }

    private function initHotelRestaurant() {
        $fileName = '宾馆饭店.xlsx';
        $data = $this->excelToArray($fileName);

        foreach ($data as $v) {
            $model = new DataDict();
            $model->dict_code = trim($v['宾馆饭店名称']);
            $model->dict_desc = trim($v['宾馆饭店名称']);
            $model->category_code = 'HotelRestaurant';
            $model->category_desc = '宾馆饭店';
            $model->district_id = $this->getRowDistrictId($v);
            $model->type = DataDict::TYPE_DISTRICT;
            $model->save();
        }

        echo "\n宾馆饭店初始化完成...\n";
    }

    private function initFamilyHotel() {
        $fileName = '家庭旅馆.xlsx';
        $data = $this->excelToArray($fileName);

        foreach ($data as $v) {
            $model = new DataDict();
            $model->dict_code = trim($v['家庭旅馆名称']);
            $model->dict_desc = trim($v['家庭旅馆名称']);
            $model->category_code = 'FamilyHotel';
            $model->category_desc = '家庭旅馆';
            $model->district_id = $this->getRowDistrictId($v);
            $model->type = DataDict::TYPE_DISTRICT;
            $model->save();
        }

        echo "\n家庭旅馆初始化完成...\n";
    }

    public function initHighSchoolGraduates() {
        $fileName = '高校毕业生.xlsx';
        $data = $this->excelToArray($fileName);

        foreach ($data as $v) {
            $model = new DataDict();
            $model->dict_code = trim($v['高校毕业生']);
            $model->dict_desc = trim($v['高校毕业生']);
            $model->category_code = 'HighSchoolGraduates';
            $model->category_desc = '高校毕业生';
            $model->district_id = $this->getRowDistrictId($v);
            $model->type = DataDict::TYPE_DISTRICT;
            $model->save();
        }

        echo "\n高校毕业生初始化完成...\n";
    }

    public function initTourismProduct() {
        $fileName = '旅游产品.xlsx';
        $data = $this->excelToArray($fileName);

        foreach ($data as $v) {
            $model = new DataDict();
            $model->dict_code = trim($v['旅游产品']);
            $model->dict_desc = trim($v['旅游产品']);
            $model->category_code = 'TourismProduct';
            $model->category_desc = '旅游产品';
            $model->district_id = $this->getRowDistrictId($v);
            $model->type = DataDict::TYPE_DISTRICT;
            $model->save();
        }

        echo "\n旅游产品初始化完成...\n";
    }

    private function initProjectConstruction() {
        $fileName = '项目建设.xlsx';
        $data = $this->excelToArray($fileName);

        foreach ($data as $v) {
            $model = new DataDict();
            $model->dict_code = trim($v['项目建设名称']);
            $model->dict_desc = trim($v['项目建设名称']);
            $model->category_code = 'ProjectConstruction';
            $model->category_desc = '项目建设';
            $model->district_id = $this->getRowDistrictId($v);
            $model->type = DataDict::TYPE_DISTRICT;
            $model->save();
        }

        echo "\n项目建设初始化完成...\n";
    }

    private function initTravelAgency() {
        $fileName = '旅行社.xlsx';
        $data = $this->excelToArray($fileName);

        $extraAttrs = ['报送员姓名','报送员电话','备注'];
        foreach ($data as $v) {
            $extra = [];
            foreach ($extraAttrs as $attr) {
                $extra[$attr] = trim($v[$attr]);
            }

            $district = District::where(['name'=>trim($v['旅行社名称'])])->first();

            $model = new DataDict();
            $model->dict_code = trim($v['旅行社名称']);
            $model->dict_desc = trim($v['旅行社名称']);
            $model->category_code = 'TravelAgency';
            $model->category_desc = '旅行社';
            if (!empty($district)) {
                $model->district_id = $district->id;
            }

//            $model->district_id = $this->getRowDistrictId($v);
            $model->extra = json_encode($extra);
            $model->type = DataDict::TYPE_CITY;
            $model->save();
        }

        echo "\n旅行社初始化完成...\n";
    }

    private function initStarredHotel() {
        $fileName = '星级饭店.xlsx';
        $data = $this->excelToArray($fileName);

        $extraAttrs = ['星级','客房数','床位数','备注'];
        foreach ($data as $v) {
            $extra = [];
            foreach ($extraAttrs as $attr) {
                $extra[$attr] = trim($v[$attr]);
            }

            $model = new DataDict();
            $model->dict_code = trim($v['饭店名称']);
            $model->dict_desc = trim($v['饭店名称']);
            $model->category_code = 'StarredHotel';
            $model->category_desc = '星级饭店';
            $model->district_id = $this->getRowDistrictId($v);
            $model->extra = json_encode($extra);
            $model->type = DataDict::TYPE_CITY;
            $model->save();
        }

        echo "\n星级饭店初始化完成...\n";
    }

    private function initALevelScenicSpot() {
        $fileName = 'A级旅游景区.xlsx';
        $data = $this->excelToArray($fileName);

        foreach ($data as $v) {
            $model = new DataDict();
            $model->dict_code = trim($v['景区点名称']);
            $model->dict_desc = trim($v['景区点名称']);
            $model->category_code = 'ALevelScenicSpot';
            $model->category_desc = 'A级旅游景区';
            $model->district_id = $this->getRowDistrictId($v);
            $model->type = DataDict::TYPE_CITY;
            $model->save();
        }

        echo "\nA级旅游景区初始化完成...\n";
    }

    private function initReportingUnit() {
        $fileName = '填报单位.xlsx';
        $data = $this->excelToArray($fileName);
        foreach ($data as $v) {
            $model = new District();
            $model->name = trim($v['填报单位名称']);
            $model->save();
        }
    }

    private function initReportingUnit2() {
        $fileName = '填报单位.xlsx';
        $data = $this->excelToArray($fileName);

        foreach ($data as $v) {
            $model = new DataDict();
            $model->dict_code = trim($v['填报单位名称']);
            $model->dict_desc = trim($v['填报单位名称']);
            $model->category_code = 'ReportingUnit';
            $model->category_desc = '填报单位';
            $model->district_id = $this->getRowDistrictId($v);
            $model->type = DataDict::TYPE_CITY;
            $model->save();
        }

        echo "\n填报单位初始化完成...\n";
    }

    private function getRowDistrictId($row) {
        if (!isset($row['所属区县']) || empty(trim($row['所属区县']))) {
            return null;
        }
        $districtName = trim($row['所属区县']);
        if (empty($districtName)) {
            return null;
        }
        $model = District::where('name','like',"%{$districtName}%")->first();
        return !empty($model) ? $model->id : null;
    }

    private function excelToArray($fileName)
    {
        $filePath = __DIR__ . '/../../../storage/app/public/dict/' . $fileName;
        $objPHPExcelReader = \PHPExcel_IOFactory::load($filePath);
        $sheet = $objPHPExcelReader->getSheet(0);
        $data = $sheet->toArray();
        $keyArr = $data[0];
        $result = [];

        foreach ($data as $k1 => $v1) {
            if ($k1 == 0) {
                continue;
            }
            $tmpData = [];
            foreach ($v1 as $k2 => $v2) {
                $tmpData[$keyArr[$k2]] = $v2;
            }
            $result[] = $tmpData;
        }

        return $result;
    }
}
